/*
* CourseStatisticsDaoImpl.java
* Created on  2014-5-15 上午9:44
* 版本       修改时间          作者      修改内容
* V1.0.1    2014-5-15       gaoxinyu    初始版本
*
*/
package com.genghis.ptas.course.dao.impl;


import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.course.dao.CourseStatisticsDao;
import com.genghis.ptas.statistic.entity.CourseStatistics;
import com.genghis.ptas.statistic.entity.Range;
import com.genghis.ptas.statistic.entity.Statistic;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

@Repository("courseStatisticsDao")
public class CourseStatisticsDaoImpl extends PageDao implements CourseStatisticsDao {


    @Override
    public int addCourseStatistics(CourseStatistics courseStatistics) {
        String sql = "INSERT INTO course_statistics(average_score, total_time, collect_number, register_number, total_number, course_id) " +
                "VALUES(:averageScore, :totalTime, :collectNumber, :registerNumber, :totalNumber, :courseId)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseStatistics), keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public CourseStatistics findCourseStatisticsById(Integer id) {
        String sql = "select * from course_statistics where id = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class), id);
    }

    @Override
    public CourseStatistics findCourseStatisticsByCourseId(Integer courseId) {
        String sql = "select * from course_statistics where course_id = ?";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class), courseId);
    }

    @Override
    public void editCourseStatistics(CourseStatistics courseStatistics) {
        String sql = "UPDATE course_statistics SET average_score = :averageScore, total_time = :totalTime, collect_number = :collectNumber, register_number = :registerNumber , " +
                " total_number = :totalNumber, quality = :quality, course_id = :courseId WHERE id = :id ";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(courseStatistics));
    }

    @Override
    public void delCourseStatistics(Integer courseId) {
        String sql = "DELETE FROM course WHERE course_id = ?";
        getJdbcTemplate().update(sql, courseId);
    }

    @Override
    public Page<CourseStatistics> findAllStatisticList(PageParameter pageParameter, CourseStatistics courseStatistics, Range range) {
        StringBuilder sql = new StringBuilder("SELECT * FROM (SELECT cs.*,c.course_name FROM course_statistics cs, course c WHERE cs.course_id=c.id) r WHERE 1=1 ");
        if (courseStatistics.getCourseName() != null && !"".equals(courseStatistics.getCourseName())) {
            sql.append(" AND r.course_name like '%").append(courseStatistics.getCourseName().trim()).append("%'");
        }
        if (courseStatistics.getTypeId() != null && !"".equals(courseStatistics.getTypeId())) {
            sql.append(" AND r.course_type = ").append(courseStatistics.getTypeId().trim()).append(" ");
        }
        if (range.getCollectLowerNumber() != null && !"".equals(range.getCollectLowerNumber())) {
            sql.append(" AND r.collect_number >=").append(range.getCollectLowerNumber().trim()).append(" ");
        }
        if (range.getCollectUpperNumber() != null && !"".equals(range.getCollectUpperNumber())) {
            sql.append(" AND r.collect_number <=").append(range.getCollectUpperNumber().trim()).append(" ");
        }
        if (range.getRegisterLowerNumber() != null && !"".equals(range.getRegisterLowerNumber())) {
            sql.append(" AND r.register_number >=").append(range.getRegisterLowerNumber().trim()).append(" ");
        }
        if (range.getRegisterUpperNumber() != null && !"".equals(range.getRegisterUpperNumber())) {
            sql.append(" AND r.register_number <=").append(range.getRegisterUpperNumber().trim()).append(" ");
        }
        if (range.getQualityId() != null && !"".equals(range.getQualityId())) {
            sql.append(" AND r.quality =").append(range.getQualityId().trim()).append(" ");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(courseStatistics), new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class));
    }

    @Override
    public CourseStatistics findNewsetAverageScoreByCourseId(Integer courseId) {
        String sql = "SELECT cs.`id`, cs.`collect_number`,cs.`course_id`,cs.`quality`,cs.`register_number`,cs.`total_number`,cs.`total_time`,AVG(cr.`score`) AS average_score\n" +
                "FROM `course_register` cr LEFT JOIN `course_statistics`cs ON cr.`course_id` = cs.`course_id` WHERE cr.`course_id`= ? GROUP BY cr.`course_id`  ";
        return getJdbcTemplate().queryForObject(sql, new BeanPropertyRowMapper<CourseStatistics>(CourseStatistics.class), courseId);
    }

    @Override
    public Page<Statistic> findUserStatisticList(PageParameter pageParameter, Statistic statistic, Range range) {
        StringBuilder sql = new StringBuilder("select * from ( SELECT re.*,co.collect_number FROM (\n" +
                "SELECT u.id userId, u.user_name, IFNULL(register_number,0) register_number FROM `sys_user` u LEFT JOIN \n" +
                "(SELECT COUNT(*) register_number,student_id FROM course_register GROUP BY `student_id`) c ON u.`id`=c.`student_id` \n" +
                "GROUP BY u.`id` \n" +
                ") re LEFT JOIN (\n" +
                "\n" +
                "SELECT u.id userId, u.user_name, IFNULL(collect_number,0) collect_number FROM `sys_user` u LEFT JOIN \n" +
                "(SELECT COUNT(*) collect_number,user_id FROM course_collect GROUP BY `user_id`) c ON u.`id`=c.`user_id` \n" +
                "GROUP BY u.`id`\n" +
                ") co ON re.userId=co.userId) r where 1=1 ");
        if (statistic.getUserName() != null && !"".equals(statistic.getUserName())) {
            sql.append(" AND r.user_name like '%").append(statistic.getUserName().trim()).append("%'");
        }
        if (range.getCollectLowerNumber() != null && !"".equals(range.getCollectLowerNumber())) {
            sql.append(" AND r.collect_number >=").append(range.getCollectLowerNumber().trim()).append(" ");
        }
        if (range.getCollectUpperNumber() != null && !"".equals(range.getCollectUpperNumber())) {
            sql.append(" AND r.collect_number <=").append(range.getCollectUpperNumber().trim()).append(" ");
        }
        if (range.getRegisterLowerNumber() != null && !"".equals(range.getRegisterLowerNumber())) {
            sql.append(" AND r.register_number >=").append(range.getRegisterLowerNumber().trim()).append(" ");
        }
        if (range.getRegisterUpperNumber() != null && !"".equals(range.getRegisterUpperNumber())) {
            sql.append(" AND r.register_number <=").append(range.getRegisterUpperNumber().trim()).append(" ");
        }
        if (range.getQualityId() != null && !"".equals(range.getQualityId())) {
            sql.append(" AND r.quality =").append(range.getQualityId().trim()).append(" ");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(statistic), new BeanPropertyRowMapper<Statistic>(Statistic.class));
    }
}
